package me.sealer.utils;

import me.sealer.model.HoleRecord;
import me.sealer.utils.enums.ColumnName;
import me.sealer.utils.enums.PrintTarget;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.read.biff.BiffException;
import jxl.write.*;

import java.io.*;
import java.util.Calendar;
import java.util.Date;

/**
 * Created by sealer on 08-05-2017.
 */
public class Excel03Util {

    /**
     * 唯一一个暴露给外部使用的方法
     *
     * @param templatePath
     * @param srcPath
     * @param destFilePath
     */
    public static void handleExcel(String templatePath, String srcPath, String destFilePath) {

        Workbook workbook = readExcel(srcPath);
        //读取sheets
        Sheet[] sheets = workbook.getSheets();
        //处理sheets
        processExcel(templatePath, sheets, destFilePath);

        workbook.close();
    }


    private static Workbook readExcel(String srcPath) {
        Workbook workbook = null;
        InputStream is = null;
        //创建excel输入流
        try {
            is = new FileInputStream(srcPath);

            //jsx的Workbook处理输入流
            workbook = Workbook.getWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return workbook;
        }

    }

    /**
     * @param templatePath
     * @param sheets
     * @param destFilePath
     */
    private static void processExcel(String templatePath, Sheet[] sheets, String destFilePath) {
        if (sheets == null) {
            return;
        }
        for (Sheet sheet : sheets) {
            processSheet(templatePath, sheet, destFilePath);
//            printSheet(sheet, PrintTarget.CONSOLE, null);
        }

    }

    /**
     * 处理每一个sheet
     *
     * @param templatePath
     * @param sheet
     * @param destFilePath
     */
    private static void processSheet(String templatePath, Sheet sheet, String destFilePath) {
        int length = sheet.getRows();//这里过滤掉空行， 待完善
        for (int i = 3; i < length; i++) {// 从3行开始, 具体情况， 未抽象
            Cell[] cells = sheet.getRow(i);
            for (int j = 0; j < cells.length; j++) {
                System.out.print(cells[j] != null ? cells[j].getContents() : null);
                System.out.print("\t");
            }
            System.out.println();
            try {
                processRows(templatePath, cells, destFilePath);
            } catch (ArrayIndexOutOfBoundsException e) {
                e.printStackTrace();
                continue;
            }
        }
    }

    /**
     * 处理每一行
     *
     * @param templatePath
     * @param cells
     * @param destFilePath
     */
    private static void processRows(String templatePath, Cell[] cells, String destFilePath) {
        HoleRecord holeRecord = assembleHoleRecord(cells);

        String destFilename = "";//待拼接的目标文件名  序号 + 桩号 + 机号 + timestamp

        Enum target = ColumnName.序号;
        String name = cells[EnumUtil.getOrdinal(target)].getContents();//序号
        //将不足4位的编号补充前置0凑足4位
        while (name.length() < 4) {
            name = "0" + name;
        }
        destFilename += name + "_";//序号

        target = ColumnName.桩号;
        name = cells[EnumUtil.getOrdinal(target)].getContents();//桩号
        destFilename += name + "_";//桩号

        target = ColumnName.机号;
        name = cells[EnumUtil.getOrdinal(target)].getContents();//机号
        destFilename += name + "_";//机号

        destFilename += Calendar.getInstance().getTimeInMillis();//添加时间戳

        destFilename += ".xls";

        createExcelFromTemplate(templatePath, holeRecord, destFilePath, destFilename);//创建目标excel


    }

    //最复杂也是最无趣的部分
    private static HoleRecord assembleHoleRecord(Cell[] cells) {
        HoleRecord holeRecord = new HoleRecord();

        String datePrefix = "1970/1/1 ";//不需要年月日的日期格式的默认年月日
        //设置no
        Enum target = ColumnName.序号;
        String s = cells[EnumUtil.getOrdinal(target)].getContents();//序号
        //将不足4位的编号补充前置0凑足4位
        while (s.length() < 4) {
            s = "0" + s;
        }
        s = "编号：" + s;
        holeRecord.setNo(s);

        //机号
        target = ColumnName.机号;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//机号
        s = "旋挖钻" + s + "#";
        holeRecord.setDrillName(s);

        //钻孔起讫时间
        target = ColumnName.日期;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//日期
//        System.out.println(s + "=========");
        String[] ss = s.split("\\.");
//        System.out.println(ss.length + "=======");
        String ds = "2017/" + ss[0] + "/" + ss[1];
        Date d = DateUtil.parseDate(ds, "yyyy/MM/dd");
        holeRecord.setStrTime(s);
        holeRecord.setDrillTime(d);

        //桩号
        target = ColumnName.桩号;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//桩号
        holeRecord.setMileage(s);

        //设计桩顶高度
        target = ColumnName.桩顶高度;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//桩顶高度
        float f = Float.valueOf(s);
        holeRecord.setDesignMileageTopHeight(f);

        //设计孔深
        target = ColumnName.设计孔深;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//设计孔深
        f = Float.valueOf(s);
        holeRecord.setDesignHoleDepth(f);

        //设计孔底高程
        target = ColumnName.桩底高度;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//桩底高度
        f = Float.valueOf(s);
        holeRecord.setDesignHoleBottomHeight(f);

        //护筒顶高程
        target = ColumnName.护筒高度;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//护筒高度
        f = Float.valueOf(s);
        holeRecord.setPileCasingTopHeight(f);

        //钻孔开始时间
        target = ColumnName.成孔开始时间;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//成孔开始时间
        ds = "2017/" + ss[0] + "/" + ss[1] + " " + s + ":00";
        d = DateUtil.parseDate(ds, "yyyy/MM/dd HH:mm:ss");
        holeRecord.setDrillBeginTime(d);

        //钻孔结束时间
        target = ColumnName.成孔结束时间;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//成孔结束时间
        ds = "2017/" + ss[0] + "/" + ss[1] + " " + s + ":00";
        d = DateUtil.parseDate(ds, "yyyy/MM/dd HH:mm:ss");
        holeRecord.setDrillEndTime(d);

        //实际孔深
        target = ColumnName.实际孔深;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//实际孔深
        f = Float.valueOf(s);
        holeRecord.setActualHoleDepth(f);
        //最后一次钻孔深度
        holeRecord.setCurrentDrillDepth(f - 24);

        //开始清孔时间
        target = ColumnName.清孔开始时间;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//清孔开始时间
//        System.out.println(s + "========start str=======");
        d = DateUtil.parseDate(datePrefix + s + ":00", "yyyy/MM/dd HH:mm:ss");
//        System.out.println(d + "========start d=======");
        holeRecord.setClearBeginTime(d);

        //结束清孔时间
        target = ColumnName.清孔结束时间;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//清孔结束时间
//        System.out.println(s + "========end str=======");
        d = DateUtil.parseDate(datePrefix + s + ":00", "yyyy/MM/dd HH:mm:ss");
//        System.out.println(d + "========end date=======");
        holeRecord.setClearEndTime(d);

        //清孔结束泥浆比重
        target = ColumnName.泥浆重;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//注入泥浆比重
        f = Float.valueOf(s);
        holeRecord.setEndMudRate(f);

        //注入泥浆比重 = 清孔结束泥浆比重 + 0.09
        f += 0.09f;
        holeRecord.setBeginMudRate(f);

        //沉渣厚度
        target = ColumnName.沉渣度;
        s = cells[EnumUtil.getOrdinal(target)].getContents();//沉渣厚度
        f = Float.valueOf(s);
        holeRecord.setAfterClearHoleDregsThickness(f);

        //TODO 组装holeRecord
        //~~~~~~
        return holeRecord;
    }

    /**
     * 从模板创建excel， 并更新数据
     *
     * @param templatePath 模板excel路径
     * @param holeRecord   待处理的数据
     * @param destFilePath 目标文件路径
     * @param destFileName 目标文件名称
     */
    private static void createExcelFromTemplate(String templatePath, HoleRecord holeRecord, String destFilePath, String destFileName) {
        Workbook templateWorkbook = readExcel(templatePath);//获取模板excel
        String destFileStr = destFilePath + destFileName;
        File destFile = new File(destFileStr);
        WritableWorkbook destWorkbook = null;
        try {
            destWorkbook = Workbook.createWorkbook(destFile, templateWorkbook);
            fillExcel(destWorkbook, holeRecord);
            destWorkbook.write();//必须执行
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                destWorkbook.close();
                templateWorkbook.close();
            } catch (WriteException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 用于填充模板文件中的空缺
     *
     * @param workbook
     * @param holeRecord
     */
    private static void fillExcel(WritableWorkbook workbook, HoleRecord holeRecord) {
        WritableSheet sheet = workbook.getSheet(0);//获取活动的可写入sheet
        //TODO 为模板中的空缺赋值
        //~~~~~~
        String content = holeRecord.getNo();
        WritableCellFormat cellFormat = new WritableCellFormat();
        try {
            cellFormat.setAlignment(Alignment.RIGHT);
            Label label = new Label(0, 1, content, cellFormat);
            sheet.addCell(label);
        } catch (WriteException e) {
            e.printStackTrace();
        }
        content = holeRecord.getDrillName();
        fillCell(sheet, content, 16, 2);
        content = DateUtil.parseString(holeRecord.getDrillTime(), "yyyy年MM月dd日");
        fillCell(sheet, content, 8, 3);
        content = holeRecord.getStrTime();
        fillCell(sheet, content, 0, 8);
        fillCell(sheet, content, 0, 9);
        fillCell(sheet, content, 0, 10);
        fillCell(sheet, content, 0, 11);
        fillCell(sheet, content, 0, 12);
        content = holeRecord.getMileage();
        fillCell(sheet, content, 6, 4);
        content = Float.toString(holeRecord.getDesignMileageTopHeight());
        fillCell(sheet, StringUtil.format(content), 13, 4);
        content = Float.toString(holeRecord.getDesignHoleDepth());
        fillCell(sheet, StringUtil.format(content), 6, 5);
        content = Float.toString(holeRecord.getDesignHoleBottomHeight());
        fillCell(sheet, StringUtil.format(content), 13, 5);
        content = Float.toString(holeRecord.getPileCasingTopHeight());
        fillCell(sheet, StringUtil.format(content), 16, 5);
        content = DateUtil.parseString(holeRecord.getDrillBeginTime(), "HH:mm:ss");
        fillCell(sheet, content.substring(0, 5), 2, 8);
        content = DateUtil.parseString(holeRecord.getDrillEndTime(), "HH:mm:ss");
        fillCell(sheet, content.substring(0, 5), 3, 12);
      /* content = holeRecord
        fillCell(sheet, content, 2, 9);
       content = holeRecord
        fillCell(sheet, content, 2, 10);
       content = holeRecord
        fillCell(sheet, content, 2, 11);
       content = holeRecord
        fillCell(sheet, content, 2, 12);

       content = holeRecord
         fillCell(sheet, content, 3, 8);
       content = holeRecord
        fillCell(sheet, content, 3, 9);
       content = holeRecord
        fillCell(sheet, content, 3, 10);
       content = holeRecord
        fillCell(sheet, content, 3, 11);
     */

        content = Float.toString(holeRecord.getActualHoleDepth());
        fillCell(sheet, StringUtil.format(content), 12, 12);
        content = Float.toString(holeRecord.getCurrentDrillDepth());
        fillCell(sheet, StringUtil.format(content), 11, 12);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - 6);
        fillCell(sheet, StringUtil.format(content), 13, 8);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - 12);
        fillCell(sheet, StringUtil.format(content), 13, 9);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - 18);
        fillCell(sheet, StringUtil.format(content), 13, 10);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - 24);
        fillCell(sheet, StringUtil.format(content), 13, 11);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - holeRecord.getActualHoleDepth());
        fillCell(sheet, StringUtil.format(content), 13, 12);


       /*content = holeRecord
        fillCell(sheet, content, 4, 8);
       content = holeRecord
        fillCell(sheet, content, 4, 9);
       content = holeRecord
        fillCell(sheet, content, 4, 10);
       content = holeRecord
        fillCell(sheet, content, 4, 11);
       content = holeRecord
        fillCell(sheet, content, 4, 12);*/

        content = DateUtil.parseString(holeRecord.getClearBeginTime(), "HH:mm:ss");
        fillCell(sheet, content.substring(0, 5), 3, 17);
        content = DateUtil.parseString(holeRecord.getClearEndTime(), "HH:mm:ss");
        fillCell(sheet, content.substring(0, 5), 8, 17);
        content = Float.toString(holeRecord.getBeginMudRate());
        fillCell(sheet, StringUtil.format(content), 3, 18);
        content = Float.toString(holeRecord.getEndMudRate());
        fillCell(sheet, StringUtil.format(content), 8, 18);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - holeRecord.getActualHoleDepth() + 0.22f);
        fillCell(sheet, StringUtil.format(content), 3, 19);
        content = Float.toString(holeRecord.getPileCasingTopHeight() - holeRecord.getActualHoleDepth() + 0.22f - holeRecord.getAfterClearHoleDregsThickness());
        fillCell(sheet, StringUtil.format(content), 8, 19);
        content = Float.toString(holeRecord.getAfterClearHoleDregsThickness());
        fillCell(sheet, StringUtil.format(content), 12, 19);

    }

    /**
     * 向sheet的第row行， 第column列插入内容e
     *
     * @param sheet
     * @param content
     * @param column
     * @param row
     */
    private static void fillCell(WritableSheet sheet, String content, int column, int row) {
        //TODO 代码待完善 区分不同类型
        Label label = new Label(column, row, content);
        try {
            sheet.addCell(label);
        } catch (WriteException e) {
            e.printStackTrace();
        }

    }

    /**
     * 把 单元格（startColumn, startRow）到单元格（endColumn, endRow）进行合并
     *
     * @param sheet
     * @param startColumn
     * @param startRow
     * @param endColumn
     * @param endRow
     */
    private static void mergeCells(WritableSheet sheet, int startColumn, int startRow, int endColumn, int endRow) {
        try {
            sheet.mergeCells(startColumn, startRow, endColumn, endRow);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 打印sheet内容
     *
     * @param sheet
     */
    private static void printSheet(Sheet sheet, PrintTarget target, String descPath) {
        for (int i = 3; i < 7; i++) {
            for (int j = 0; j < 27; j++) {
                PrintUtil.printString("(" + j + "," + i + ")" + ":" + sheet.getCell(j, i).getContents(), target, descPath + "\t");
            }
            System.out.println();
        }
    }

}

